package com.honey.compilation.sql.dml;

import java.sql.Types;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.honey.compilation.sql.SqlColumn;
import com.honey.compilation.sql.SqlElement;
import com.honey.compilation.sql.SqlKeyWord;
import com.honey.compilation.sql.SqlTable;
import com.honey.core.builder.CharacterBuilder;
import com.honey.core.builder.CharacterBuilderFactory;
import com.honey.core.compiler.CompilationException;
import com.honey.core.utils.StringUtility;

/**
 * 查询条件
 * @author Administrator
 *
 */
public class Condition extends SqlElement{

	/** 条件语句结构 */
	private List<Structure> clause;
	
	/** 是否开启别名(在SQL 92 中只有select有别名) */
	private boolean isEnableAlias=true;
	
	/** 用于Select语句生成alias通知条件语句更新alias */
	private Map<String, List<Structure>> map;
	
	/**
	 * 构造函数
	 */
	public Condition(){
		clause = new ArrayList<Structure>();
		map = new HashMap<String, List<Structure>>();
	}
	
	/**
	 * or连接后续条件语句
	 * @return
	 */
	public Condition or( ){
		clause.add(new OrStructure());
		return this;
	}
	
	/**
	 * 是否开启别名(在SQL 92 中只有select有别名)
	 * @param enable 是否开启别名 true:是 false:否
	 * @return
	 */
	protected Condition setEnableAlias(boolean enable){
		this.isEnableAlias = enable;
		return this;
	}
	
	/**
	 * 是否开启别名(在SQL 92 中只有select有别名)
	 * @return
	 */
	protected boolean isEnableAlias(){
		return this.isEnableAlias ;
	}

	/**
	 * 用于Select语句生成alias通知条件语句更新alias
	 * @param table
	 * @return
	 * @throws CompilationException 
	 */
	private String createKey(SqlTable table) throws CompilationException{
		CharacterBuilder cb = CharacterBuilderFactory.createC16StringBuilder();
		if(table != null ){
			cb.append(table.compiledContent(0));
		}
		return cb.toString();
	}
	
	/**
	 * 用于Select语句生成alias通知条件语句更新alias
	 * @param table
	 * @param structure
	 * @return
	 * @throws CompilationException 
	 */
	private Condition putStructure( SqlTable table, Structure structure) throws CompilationException{
		String key = createKey(table);
		List<Structure> l = map.get(key);
		if( l == null ){
			l = new ArrayList<Structure>();
			map.put(key, l);
		}
		if( structure instanceof OnlyColumnStructure ){
			l.add(structure);
		}
		return this;
	}
	
	/**
	 * 用于Select语句生成alias通知条件语句更新alias
	 * @param table
	 * @param tableAlias
	 * @return
	 * @throws CompilationException 
	 */
	public Condition setTableAlias(SqlTable table,String tableAlias ) throws CompilationException{
		String key = createKey(table);
		List<Structure> l = map.get(key);
		if(l == null )return this;
		for(Structure structure : l){
			if(structure instanceof OnlyColumnStructure ){
				OnlyColumnStructure c = (OnlyColumnStructure)structure;
				c.setTableAlias(tableAlias);
			}
		}
		return this;
	}
	
	/**
	 * 只有一个值条件语句
	 * @param table 表
	 * @param tableAlias 别名
	 * @param column 列
	 * @param ct 条件
	 * @param value 值
	 * @return
	 * @throws CompilationException
	 */
	private Condition createConditionWithValue(SqlTable table,String tableAlias , SqlColumn column,ConditionType ct,String value) throws CompilationException{
		if(column== null || value == null ) return this;
		SelectColumn sc= null ;
		if(column instanceof SelectColumn){
			sc = (SelectColumn)column ;
		}else{
			sc = new SelectColumn(column.getName().getValue(),column.getSqlType());
		}
		OneValueStructure structure = new OneValueStructure(table,sc,ct,value);
		if( StringUtility.stringHasValue(tableAlias) ){
			structure.setTableAlias(tableAlias);
		}else{
			structure.setTableAlias(sc.getAlias());
		}
		clause.add( structure  );
		putStructure(table, structure);
		return this;
	}
	
	/**
	 * Like 条件语句
	 * @param table 表
	 * @param tableAlias 别名 
	 * @param column 列
	 * @param ct 条件
	 * @param value 值
	 * @return
	 * @throws CompilationException
	 */
	private Condition createConditionWithLike(SqlTable table,String tableAlias , SqlColumn column,ConditionType ct,String value) throws CompilationException{
		if(column== null || value == null ) return this;
		SelectColumn sc= null ;
		if(column instanceof SelectColumn){
			sc = (SelectColumn)column ;
		}else{
			sc = new SelectColumn(column.getName().getValue(),column.getSqlType());
		}
		LikeStructure structure = new LikeStructure(table,sc,ct,value);
		if( StringUtility.stringHasValue(tableAlias) ){
			structure.setTableAlias(tableAlias);
		}else{
			structure.setTableAlias(sc.getAlias());
		}
		clause.add( structure  );
		putStructure(table, structure);
		return this;
	}
	
	/**
	 * in 条件语句
	 * @param table 表
	 * @param tableAlias 别名
	 * @param column 列
	 * @param ct 条件
	 * @param values 值
	 * @return
	 * @throws CompilationException
	 */
	private Condition createConditionWithIn(SqlTable table,String tableAlias , SqlColumn column,ConditionType ct,String ...values ) throws CompilationException{
		if(column== null || values == null ) return this;
		SelectColumn sc= null ;
		if(column instanceof SelectColumn){
			sc = (SelectColumn)column ;
		}else{
			sc = new SelectColumn(column.getName().getValue(),column.getSqlType());
		}
		InStructure structure = new InStructure(table,sc,ct,values);
		if( StringUtility.stringHasValue(tableAlias) ){
			structure.setTableAlias(tableAlias);
		}else{
			structure.setTableAlias(sc.getAlias());
		}
		clause.add( structure  );
		putStructure(table, structure);
		return this;
	}

	/**
	 * Between 条件语句
	 * @param table 表
	 * @param tableAlias 别名
	 * @param column 列
	 * @param ct 条件
	 * @param value1
	 * @param value2
	 * @return
	 * @throws CompilationException
	 */
	private Condition createConditionWithBetween(SqlTable table,String tableAlias , SqlColumn column,ConditionType ct,String value1,String value2 ) throws CompilationException{
		if(column== null || ! StringUtility.stringHasValue(value1) || ! StringUtility.stringHasValue(value2)  ) return this;
		SelectColumn sc= null ;
		if(column instanceof SelectColumn){
			sc = (SelectColumn)column ;
		}else{
			sc = new SelectColumn(column.getName().getValue(),column.getSqlType());
		}
		BetweenStructure structure = new BetweenStructure(table,sc,ct,value1,value2);
		if( StringUtility.stringHasValue(tableAlias) ){
			structure.setTableAlias(tableAlias);
		}else{
			structure.setTableAlias(sc.getAlias());
		}
		clause.add( structure  );
		putStructure(table, structure);
		return this;
	}
	

	/**
	 * 生成条件
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 * @throws CompilationException 
	 */
	private Condition createConditionNotValue(SqlTable table,String tableAlias , SqlColumn column,ConditionType ct) throws CompilationException{
		if(column== null ) return this;
		SelectColumn sc= null ;
		if(column instanceof SelectColumn){
			sc = (SelectColumn)column ;
		}else{
			sc = new SelectColumn(column.getName().getValue(),column.getSqlType());
		}
		OnlyColumnStructure structure = new OnlyColumnStructure(table,sc,ct);
		if( StringUtility.stringHasValue(tableAlias) ){
			structure.setTableAlias(tableAlias);
		}else{
			structure.setTableAlias(sc.getAlias());
		}
		clause.add( structure  );
		putStructure(table, structure);
		return this;
	}
	
	/**
	 * and 新的条件组合
	 * @param condition
	 * @return
	 * @throws CompilationException 
	 */
	public Condition andCondition(Condition condition) throws CompilationException{
		if(condition != null){
			condition.setEnableAlias(this.isEnableAlias);
			ConditionStructure structure = new ConditionStructure(condition,SqlKeyWord.AND);
			clause.add( structure  );
			for(Structure s : structure.condition.clause ){
				if( s instanceof OnlyColumnStructure ){
					OnlyColumnStructure oc =(OnlyColumnStructure)s ;
					putStructure(oc.table, s);
				}
			}
		}
		return this;
	}
	
	/**
	 * or 新的条件组合
	 * @param condition
	 * @return
	 * @throws CompilationException 
	 */
	public Condition orCondition(Condition condition) throws CompilationException{
		if(condition != null){
			condition.setEnableAlias(this.isEnableAlias);
			ConditionStructure structure = new ConditionStructure(condition,SqlKeyWord.OR);
			clause.add( structure  );
			for(Structure s : structure.condition.clause ){
				if( s instanceof OnlyColumnStructure ){
					OnlyColumnStructure oc =(OnlyColumnStructure)s ;
					putStructure(oc.table, s);
				}
			}
		}
		return this;
	}
	
	
	/**
	 * 等于
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 只
	 * @return
	 */
	public Condition andEqual(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithValue(table, tableAlias, column, ConditionType.EQUAL_TO, value);
	}
	

	/**
	 * 等于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param table 表 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andEqual(SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andEqual(table, null, column, value);
		
	}
	/**
	 * 等于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andEqual(String tableAlias , SqlColumn column,String value) throws CompilationException{
		return andEqual(null, tableAlias, column, value);
		
	}
	
	/**
	 * 等于
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andEqual( SqlColumn column,String value) throws CompilationException{
		return andEqual(null, null, column, value);
	}
	
	/**
	 * 不等于
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotEqual(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithValue(table, tableAlias, column, ConditionType.NOT_EQUAL_TO, value);
	}
	
	/**
	 * 不等于
	 * @param table 表
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotEqual(SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andNotEqual(table,null, column, value);
	}
	
	/**
	 * 不等于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotEqual(String tableAlias , SqlColumn column,String value) throws CompilationException {
		return andNotEqual(null, tableAlias, column, value);
		
	}
	
	/**
	 * 不等于
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotEqual( SqlColumn column,String value)throws CompilationException {
		return andNotEqual(null, null, column, value);
	}

	
	

	/**
	 * 大于
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThan(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithValue(table, tableAlias, column, ConditionType.GREATER_THAN, value);
	}
	
	/**
	 * 大于
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThan(SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andGreaterThan(table, null, column, value);
	}
	
	/**
	 * 大于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThan(String tableAlias , SqlColumn column,String value) throws CompilationException{
		return andGreaterThan(null, tableAlias, column, value);
	}
	
	/**
	 * 大于
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThan( SqlColumn column,String value) throws CompilationException{
		return andGreaterThan(null, null, column, value);
	}

	
	/**
	 * 大于等于
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThanOrEqualTo(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithValue(table, tableAlias, column, ConditionType.GREATER_THAN_OR_EQUAL_TO, value);
	}
	
	/**
	 * 大于等于
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThanOrEqualTo(SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andGreaterThanOrEqualTo(table, null, column, value);
	}
	
	/**
	 * 大于等于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThanOrEqualTo(String tableAlias , SqlColumn column,String value) throws CompilationException{
		return andGreaterThanOrEqualTo(null, tableAlias, column, value);
		
	}
	
	/**
	 * 大于等于
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andGreaterThanOrEqualTo( SqlColumn column,String value) throws CompilationException{
		return andGreaterThanOrEqualTo(null, null, column, value);
	}

	
	
	/**
	 * 小于
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThan(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithValue(table, tableAlias, column, ConditionType.LESS_THAN, value);
	}
	
	/**
	 * 小于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThan(SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andLessThan(table, null, column, value);
		
	}
	/**
	 * 小于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThan(String tableAlias , SqlColumn column,String value) throws CompilationException{
		return andLessThan(null, tableAlias, column, value);
		
	}
	
	/**
	 * 小于
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThan( SqlColumn column,String value) throws CompilationException{
		return andLessThan(null, null, column, value);
	}
	
	
	/**
	 * 小于等于
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThanOrEqualTo(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithValue(table, tableAlias, column, ConditionType.LESS_THAN_OR_EQUAL_TO, value);
	}
	
	/**
	 * 小于等于
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThanOrEqualTo(SqlTable table, SqlColumn column,String value)throws CompilationException{
		return andLessThanOrEqualTo(table, null, column, value);
		
	}
	
	/**
	 * 小于等于
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThanOrEqualTo(String tableAlias , SqlColumn column,String value)throws CompilationException{
		return andLessThanOrEqualTo(null, tableAlias, column, value);
		
	}
	
	/**
	 * 小于等于
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andLessThanOrEqualTo( SqlColumn column,String value) throws CompilationException{
		return andLessThanOrEqualTo(null, null, column, value);
	}
	
	
	
	/**
	 * 为null
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNull(SqlTable table,String tableAlias , SqlColumn column) throws CompilationException{
		return createConditionNotValue(table, tableAlias, column,  ConditionType.IS_NULL);
	}
	
	/**
	 * 为null
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNull(SqlTable table, SqlColumn column) throws CompilationException{
		return andIsNull(table, null, column);
		
	}
	
	/**
	 * 为null
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNull(String tableAlias , SqlColumn column) throws CompilationException{
		return andIsNull(null, tableAlias, column);
		
	}
	
	/**
	 * 为null
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNull( SqlColumn column) throws CompilationException{
		return andIsNull(null, null, column);
	}
	
	/**
	 * 不为null
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNotNull(SqlTable table,String tableAlias , SqlColumn column) throws CompilationException{
		return createConditionNotValue(table, tableAlias, column,  ConditionType.IS_NOT_NULL);
	}
	
	/**
	 * 不为null
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNotNull(SqlTable table, SqlColumn column) throws CompilationException{
		return andIsNotNull(table, null, column);
		
	}
	
	/**
	 * 不为null
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNotNull(String tableAlias , SqlColumn column) throws CompilationException{
		return andIsNotNull(null, tableAlias, column);
	}
	
	/**
	 * 不为null
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andIsNotNull( SqlColumn column ) throws CompilationException{
		return andIsNotNull(null, null, column);
	}
	
	/**
	 * like
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andLike(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithLike(table, tableAlias, column, ConditionType.LIKE, value);
	}
	
	/**
	 * like
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andLike( SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andLike(table, null, column, value);
		
	}
	
	/**
	 * like
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andLike(String tableAlias , SqlColumn column,String value) throws CompilationException{
		return andLike(null, tableAlias, column, value);
		
	}
	
	/**
	 * like
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andLike( SqlColumn column,String value) throws CompilationException{
		return andLike(null, null, column, value);
	}
	
	/**
	 * not like
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotLike(SqlTable table,String tableAlias , SqlColumn column,String value) throws CompilationException{
		return createConditionWithLike(table, tableAlias, column, ConditionType.NOT_LIKE, value);
	}
	
	
	/**
	 * not like
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotLike(SqlTable table, SqlColumn column,String value) throws CompilationException{
		return andNotLike(table, null, column, value);
		
	}
	
	/**
	 * not like
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotLike(String tableAlias , SqlColumn column,String value) throws CompilationException{
		return andNotLike(null, tableAlias, column, value);
		
	}
	
	/**
	 * Not like
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotLike( SqlColumn column,String value) throws CompilationException{
		return andNotLike(null, null, column, value);
	}
	
	
	/**
	 * in
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andIn(SqlTable table,String tableAlias , SqlColumn column,String ...values) throws CompilationException{
		return createConditionWithIn(table, tableAlias, column, ConditionType.IN, values);
	}
	
	/**
	 * in
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andIn(SqlTable table, SqlColumn column,String ...values) throws CompilationException{
		return andIn(table, null, column, values);
		
	}
	
	/**
	 * in
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andIn(String tableAlias , SqlColumn column,String ...values) throws CompilationException{
		return andIn(null, tableAlias, column, values);
		
	}
	
	/**
	 * in
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andIn( SqlColumn column,String ...values) throws CompilationException{
		return andIn(null, null, column, values);
	}
	
	
	/**
	 *  not in
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotIn(SqlTable table,String tableAlias , SqlColumn column,String ...values) throws CompilationException{
		return createConditionWithIn(table, tableAlias, column, ConditionType.NOT_IN, values);
	}
	
	/**
	 * not in
	 * @param table 表
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotIn(SqlTable table, SqlColumn column,String ...values) throws CompilationException{
		return andNotIn(table, null, column, values);
		
	}
	
	/**
	 * not in
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotIn(String tableAlias , SqlColumn column,String ...values) throws CompilationException{
		return andNotIn(null, tableAlias, column, values);
		
	}
	
	/**
	 * Not in
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotIn( SqlColumn column,String ...values) throws CompilationException{
		return andNotIn(null, null, column, values);
	}
	
	/**
	 * between
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value1 第一个值
	 * @param value2 第二个值
	 * @return
	 * @throws CompilationException
	 */
	public Condition andBetween(SqlTable table,String tableAlias , SqlColumn column,String value1,String value2 ) throws CompilationException{
		return createConditionWithBetween(table, tableAlias, column, ConditionType.BETWEEN, value1,value2);
	}

	/**
	 * between
	 * @param table 表 
	 * @param column  列
	 * @param value1 第一个值
	 * @param value2 第二个值
	 * @return
	 */
	public Condition andBetween(SqlTable table, SqlColumn column,String value1,String value2) throws CompilationException{
		return andBetween(table, null, column, value1,value2);
		
	}
	
	/**
	 * between
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andBetween(String tableAlias , SqlColumn column,String value1,String value2) throws CompilationException{
		return andBetween(null, tableAlias, column, value1,value2);
		
	}
	
	/**
	 * between 
	 * @param column 列
	 * @param value1 第一个值
	 * @param value2 第二个值
	 * @return
	 * @throws CompilationException
	 */
	public Condition andBetween( SqlColumn column,String value1,String value2) throws CompilationException{
		return andBetween(null, null, column,value1,value2);
	}
	
	/**
	 * not between
	 * @param table 表
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉)
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotBetween(SqlTable table,String tableAlias , SqlColumn column,String value1,String value2) throws CompilationException{
		return createConditionWithBetween(table, tableAlias, column, ConditionType.NOT_BETWEEN, value1,value2);
	}

	/**
	 * not BETWEEN
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotBetween(SqlTable table, SqlColumn column,String value1,String value2) throws CompilationException{
		return andNotBetween(table, null , column, value1,value2);
		
	}
	
	/**
	 * not BETWEEN
	 * @param tableAlias 表的别名(只有select语句才有别名. 如果select中设定了别名,select会通知条件语句把alias替换掉) 
	 * @param column  列
	 * @param value 值
	 * @return
	 */
	public Condition andNotBetween(String tableAlias , SqlColumn column,String value1,String value2) throws CompilationException{
		return andNotBetween(null, tableAlias, column, value1,value2);
		
	}
	
	/**
	 * not between
	 * @param column 列
	 * @param value 值
	 * @return
	 */
	public Condition andNotBetween( SqlColumn column,String value1,String value2) throws CompilationException{ 
		return andNotBetween(null, null, column,value1,value2);
	}
	
	/**
	 * 不支持注释
	 */
	@Override
	public List<String> getSqlDocLines() {
		return new ArrayList<String>();
    }
	
	/**
	 * 不支持注释
	 */
	@Override
	public void addSqlDocLine(String ...sqlDocLines) {
		
	}
	/**
	 * 条件语句不支持注释
	 */
	@Override
	public void addFormattedSqlDocLines(CharacterBuilder sb, int indentLevel ){
		
	}
	
	public void clear(){
		clause.clear();
		map.clear();
	}
	
	
	@Override
	public CharacterBuilder compiledContent(int indentLevel) throws CompilationException {
		CharacterBuilder answer  = CharacterBuilderFactory.createC16StringBuilder();
		boolean b = false ;
		for(Structure s : clause ){
			if(b){
				CharacterBuilder c= s.getFormattedContent();
				c.delete(0, 4);
				answer.append(c);
				b = false;
				
			}else{
				answer.append(s.getFormattedContent());
			}
				
			if(s instanceof OrStructure ){
				b = true;
			}
		}
		if(answer.indexOf(SqlKeyWord.OR.toString())==0 ){
			answer.delete(0, 3);
		}
		if(answer.indexOf(SqlKeyWord.AND.toString())==0 ){
			answer.delete(0, 4);
		}
		
		return answer;
	}

	/**
	 * 各种条件接口
	 * @author Administrator
	 *
	 */
	private interface  Structure{
		
		public CharacterBuilder getFormattedContent() throws CompilationException; 
	}
	
	/**
	 * or 结构
	 * @author Administrator
	 *
	 */
	private class OrStructure implements Structure{
		
		public CharacterBuilder getFormattedContent() {
			
			return  CharacterBuilderFactory.createC16StringBuilder().append(SqlKeyWord.OR);
		}
	}
	
	/**
	 * 组合条件结构
	 * @author Administrator
	 *
	 */
	private class ConditionStructure implements Structure{
		
		private Condition condition ;
		
		private SqlKeyWord connect= SqlKeyWord.OR;
		
		ConditionStructure( Condition condition ,SqlKeyWord connect){
			this.condition = condition;
			if(connect==SqlKeyWord.OR || connect==SqlKeyWord.AND){
				this.connect  =  connect;
			}
		}
		public CharacterBuilder getFormattedContent()throws CompilationException {
			CharacterBuilder answer = CharacterBuilderFactory.createC16StringBuilder();
			if(condition != null){
				condition.setEnableAlias(isEnableAlias());
				answer.append(connect)
				.append(SqlKeyWord.OPERATION_PAREN_LEFT)
				.append(condition.compiledContent(0))
				.append(SqlKeyWord.OPERATION_PAREN_RIGHT)
				.append(SqlKeyWord.OPERATION_SPACE)
				;
			}
			
			return answer;
		}
	}
	
	
	
	/**
	 * 没有值的结构
	 * @author Administrator
	 *
	 */
	private class OnlyColumnStructure implements Structure{
		
		/**
		 * 表名称
		 */
		protected SqlTable table; 
		
		/**
		 * 表别名
		 */
		protected String tableAlias ;
		
		/**
		 * 列
		 */
		protected SelectColumn column;

		/**
		 * 判断类型
		 */
		protected ConditionType type ;
		
		
		OnlyColumnStructure(SqlTable table,SelectColumn column,ConditionType type ){
			this.table = table;
			this.column = column ;
			this.type = type	;
		}
		
		void setTableAlias(String tableAlias) {
			this.tableAlias = tableAlias;
		}
		
		SelectColumn getColumn() {
			return column;
		}

		public CharacterBuilder getFormattedContent() throws CompilationException {
			CharacterBuilder answer  = CharacterBuilderFactory.createC16StringBuilder();
			answer.append(SqlKeyWord.AND);
			if(StringUtility.stringHasValue(this.tableAlias) && isEnableAlias() ){
				column.setTableName(this.tableAlias);
			}else if( this.table != null){
				CharacterBuilder tb =  this.table.compiledContent(0);
				tb.delete(tb.length()-1);//去掉最后一个空格
				column.setTableName(tb.toString());
			}
			column.setEnableAlias(false);
			answer.append(column.compiledContent(0))
			.append(type);
			
			return answer;
		}
		
	}

	/**
	 * 只有一个值的结构 
	 * @author Administrator
	 *
	 */
	private class OneValueStructure extends OnlyColumnStructure{
		
		private String value ;
		
		OneValueStructure(SqlTable table, SelectColumn column,ConditionType type,String value ) {
			super(table, column, type);
			this.value = value;
		}
		
		String getValue() {
			return value;
		}
		
		@Override
		public CharacterBuilder getFormattedContent() throws CompilationException {
			CharacterBuilder answer  = super.getFormattedContent();
			boolean b = !getColumn().isNumber();
			if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
			answer.append(getValue());
			if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
			
			answer.append(SqlKeyWord.OPERATION_SPACE);
			return answer; 
		}
	}
	
	/**
	 * like 结构
	 * @author Administrator
	 *
	 */
	private class LikeStructure extends OneValueStructure{
		LikeStructure(SqlTable table, SelectColumn column, ConditionType type, String value) {
			super(table, column, type, value);
		}
		@Override
		SelectColumn getColumn() {
			SelectColumn sc = super.getColumn();
			SelectColumn nsc=  new SelectColumn(sc.getName().getValue(),Types.VARCHAR );
			nsc.setTableName(sc.getTableName());
			nsc.setAlias(sc.getAlias());
			
			return nsc;
		}
		@Override
		String getValue() {
			return "%"+super.getValue()+"%";
		}
	}
	
	/**
	 * in 结构
	 * @author Administrator
	 *
	 */
	private class InStructure extends OnlyColumnStructure{
		private List<String> invalue = new ArrayList<String>();
		
		InStructure(SqlTable table, SelectColumn column,ConditionType type) {
			super(table, column, type);
		}
		InStructure(SqlTable table, SelectColumn column,ConditionType type,String ...values) {
			super(table, column, type);
			addValue(values) ;
		}
		void addValue(String ...values ) {
			for(String value :values){
				if( value != null)
					invalue.add(value);
			}
		}

		@Override
		public CharacterBuilder getFormattedContent() throws CompilationException {
			CharacterBuilder answer  = super.getFormattedContent();
			boolean b = !getColumn().isNumber();
			if(invalue.size()>0){
				answer.append(SqlKeyWord.OPERATION_PAREN_LEFT);
				boolean comma = false;
				for(String value :invalue ){
					if (comma){answer.append(SqlKeyWord.OPERATION_COMMA);}else comma = true;
					
					if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
					answer.append(value);
					if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
				}
				answer
				.append(SqlKeyWord.OPERATION_PAREN_RIGHT)
				.append(SqlKeyWord.OPERATION_SPACE);
			}
			return answer; 
		}
		
	}
	
	/**
	 * between 结构
	 * @author Administrator
	 *
	 */
	private class BetweenStructure extends OnlyColumnStructure{
		
		private String value1 ;
		
		private String value2 ;
		
		BetweenStructure(SqlTable table, SelectColumn column,ConditionType type,String value1 , String value2) {
			super(table, column, type);
			this.value1 = value1;
			this.value2 = value2;
		}
		
		@Override
		public CharacterBuilder getFormattedContent() throws CompilationException{
			CharacterBuilder answer  = super.getFormattedContent();
			boolean b = !getColumn().isNumber();
			if(StringUtility.stringHasValue(value1) && StringUtility.stringHasValue(value2) ){
				if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
				answer.append(value1 );
				if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
				
				answer.append(SqlKeyWord.OPERATION_SPACE).append(SqlKeyWord.AND);
				if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
				answer.append(value2);
				if( b )answer.append(SqlKeyWord.OPERATION_SINGLE_QUOTES);
				
				answer.append(SqlKeyWord.OPERATION_SPACE);
			} 
			return answer; 
		}
		
	}
	
	private enum ConditionType{
		 /**
		 * is null 
		 */
		IS_NULL ((short)0 ,"IS NULL"),

		/**
		 * is not null 
		 */
		IS_NOT_NULL ((short)1 , "IS NOT NULL"),

		/**
		 * (
		 */
		EQUAL_TO ((short)2 , "="),

		/**
		 * <> 
		 */
		NOT_EQUAL_TO ((short)3 , "<>"),

		/**
		 * > 
		 */
		GREATER_THAN ((short)4 , ">"),

		/**
		 * >= 
		 */
		GREATER_THAN_OR_EQUAL_TO ((short)5 , ">="),

		/**
		 * <
		 */
		LESS_THAN ((short)6 , "<"),

		/**
		 * <=
		 */
		LESS_THAN_OR_EQUAL_TO ((short)7 , "<="),

		/**
		 * in
		 */
		IN ((short)8 , "IN"),

		/**
		 * not in
		 */
		NOT_IN ((short)9 , "NOT IN"),

		/**
		 * between
		 */
		BETWEEN ((short)10 , "BETWEEN"),

		/**
		 * not between
		 */
		NOT_BETWEEN ((short)11 , "NOT BETWEEN"),

		/**
		 * like
		 */
		LIKE ((short)12 , "LIKE"),
		
		
		/**
		 * not like
		 */
		NOT_LIKE ((short)13 , "NOT LIKE"),
		
		;
		
		private final String typeName ;
		
		private final short type;
		
		private ConditionType(short type, String typeName){
			this.typeName = typeName;
			this.type = type;
		}
		
		public String getTypeName() {
			return typeName;
		}
		
		@Override
		public String toString() {
			return this.typeName+SqlKeyWord.OPERATION_SPACE;
		}
	}
}
